package com.rg1906.dao.Impl;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.rg1906.dao.TeacherListDao;
import com.rg1906.entity.Teacher;
import com.rg1906.utils.DataSourceC3P0ThreadLocal;

public class TeacherListDaoImpl implements TeacherListDao{
	DataSource dataSource = DataSourceC3P0ThreadLocal.getDataSource();
	QueryRunner qr = new QueryRunner(dataSource);
	public List<Teacher> QueryTeacher(int pageNumberInt, int pageSizeInt, String likeName, String teacherNum, String gradeName) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("t.uid,");
		sb.append("t.id, ");
		sb.append("t.teacherName,");
		sb.append("t.teacherNum,");
		sb.append("t2.gradename,");
		sb.append("t.phone,");
		sb.append("t.sex,");
		sb.append("t.age,");
		sb.append("t.address ");
		sb.append("FROM ");
		sb.append("t_teacher t ");
		sb.append("LEFT JOIN t_grade t2 ON t.gid = t2.id where 1=1 ");
		if(likeName!=null&&!"".equals(likeName)) {
			sb.append("and t.teacherName like '%"+likeName+"%' ");
		}
		if(teacherNum!=null&&!"".equals(teacherNum)) {
			sb.append("and t.teacherNum like '%"+teacherNum+"%' ");
		}
		if(gradeName!=null&&!"".equals(gradeName)) {
			sb.append("and t2.gradename like '"+gradeName+"' ");
		}
		sb.append("LIMIT ?,? ");
		Object[] par= {pageNumberInt*pageSizeInt,pageSizeInt};
		return qr.query(sb.toString(), new BeanListHandler<>(Teacher.class),par);
	}
	public Long getCount(String likeName, String teacherNum, String gradeName) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("count(0) ");
		sb.append("FROM ");
		sb.append("t_teacher t ");
		sb.append("LEFT JOIN t_grade t2 ON t.gid = t2.id ");
		if(likeName!=null&&!"".equals(likeName)) {
			sb.append("and t.teacherName like '%"+likeName+"%' ");
		}
		if(teacherNum!=null&&!"".equals(teacherNum)) {
			sb.append("and t.teacherNum like '%"+teacherNum+"%' ");
		}
		if(gradeName!=null&&!"".equals(gradeName)) {
			sb.append("and t2.gradename like '"+gradeName+"' ");
		}
		return qr.query(sb.toString(), new ScalarHandler<>());
		
	}
}
